package org.anyline.help.test;

import org.anyline.data.jdbc.util.JDBCUtil;
import org.anyline.data.param.ConfigBuilder;
import org.anyline.data.param.ConfigStore;
import org.anyline.data.param.TableBuilder;
import org.anyline.data.param.init.DefaultConfigStore;
import org.anyline.data.prepare.RunPrepare;
import org.anyline.data.run.Run;
import org.anyline.data.runtime.DataRuntime;
import org.anyline.data.runtime.RuntimeHolder;
import org.anyline.entity.*;
import org.anyline.metadata.Table;
import org.anyline.metadata.type.init.StandardTypeMetadata;
import org.anyline.proxy.ServiceProxy;
import org.anyline.service.AnylineService;
import org.anyline.simple.help.HelpApplication;
import org.anyline.util.ConfigTable;
import org.anyline.util.DateUtil;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.sql.*;
import java.time.ZonedDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@SpringBootTest(classes = HelpApplication.class)
public class HelpTest {
    @Autowired
    private AnylineService service          ;

    public static void main(String[] args) {
        split();
    }
    public static void split(){
        String str ="COUNT( DISTINCT IF (path like '%,%',SUBSTRING_INDEX(path, ',', -1 ),null)) AS gun_sum, id as code";
        System.out.println(str);
        String regex = "(?<!['\\(])[,](?!['])";
        String[] result = str.split(regex);
        for (String s : result) {
            System.out.println(s);
        }
    }
    @Test
    public void test1(){
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        ConfigTable.IS_UPPER_KEY = false;
        ConfigTable.IS_SQL_DELIMITER_OPEN = true;
        DataSet set = ServiceProxy.querys("crm_user");
        System.out.println(set);
         set = ServiceProxy.querys(TableBuilder.init("crm_user(m.id as code, F.GP as GP) as m").left("CRM_GP as f","m.id = f.id" ).build());
        System.out.println(set);
    }
    @Test
    public  void config2json(){
        ConfigStore configs = new DefaultConfigStore();
        configs.and("ID", 1);
        configs.isNull("USER_CODE");
        configs.like("NAME", "ZH");
        configs.in("TYPE_CODE", "1,2,3".split(","));
        ConfigStore ors = new DefaultConfigStore();
        ors.and("A", 1);
        ors.or("B", 2);
        configs.or(ors);
        String json = configs.json(true);
        System.out.println(json);
        configs = ConfigBuilder.build(json);
        json = configs.json(true);
        System.out.println("------------");
        System.out.println(json.equals(json));
        String sql = configs.getConfigChain().createAutoConditionChain().getRunText(null, null, false);
        System.out.println(sql);
    }
    @Test
    public  void isNull(){
        String json = "{\"conditions\":{\"join\":\"AND\",\"items\":[{\"join\":\"AND\",\"var\":\"CODE\",\"compare\":90,\"values\":[],\"over_condition\":false,\"over_value\":true,\"parser\":{\"var\":\"CODE\",\"compare\":90,\"join\":\"AND\",\"swt\":\"IGNORE\"}}]}}";
        ConfigStore configs = ConfigBuilder.build(json);
        service.query("crm_user", configs);
    }
    @Test
    public void config2sql(){
        String json = "{\"conditions\":{\"items\":[{\"items\":[],\"join\":\"AND\"}],\"join\":\"AND\"}}";
        RunPrepare prepare = TableBuilder.init("crm_user").build().condition(ConfigBuilder.build(json));
        RunPrepare prepare2 = TableBuilder.init("crm_user").build().condition(ConfigBuilder.build(json));
        prepare.union(prepare2);
        ServiceProxy.querys(prepare);
        ConfigStore configs = new DefaultConfigStore();
        configs.and("ID", 1);
        configs.columns("ID","CODE");
        String sql = configs.getConfigChain().createAutoConditionChain().getRunText(null, RuntimeHolder.runtime(), false);
        //System.out.println(sql);
        json = configs.json();
        configs = ConfigBuilder.build(json);
        ServiceProxy.querys("crm_user", configs);
    }
    @Test
    public void union(){
        unioninit();
        DataSet set = new DataSet();
        for(int i=0; i<100; i++){
            DataRow row = set.add();
            row.put("NAME", i);
        }
        service.insert("sso_user", set);
        service.insert("hr_user", set);
        RunPrepare p1 = TableBuilder.init("SSO_USER(ID, NAME)").build().condition(new DefaultConfigStore().ge("ID", 1));
        RunPrepare p2 = TableBuilder.init("HR_USER(ID, NAME)").build().condition(new DefaultConfigStore().ge("ID", 2));
        RunPrepare p3 = TableBuilder.init("HR_USER(ID, NAME)").build().condition(new DefaultConfigStore().ge("ID", 3));
        p1.union(p2).union(p3);
        ConfigStore configs = new DefaultConfigStore().order("ID", "DESC").page(5, 10);
        service.querys(p1, configs);
        p1 = TableBuilder.init("SSO_USER(NAME, count(*) as QTY)").build().condition(new DefaultConfigStore().ge("ID", 1).group("NAME"));
        p3 = TableBuilder.init("HR_USER(NAME, COUNT(*) AS CNT)").build().condition(new DefaultConfigStore().ge("ID", 2)).group("NAME");
        p1.union(p3);
        configs = new DefaultConfigStore().order("NAME", "DESC").page(5, 10);
        service.querys(p1, configs);
    }
    @Test
    public void group(){
        service.querys("SSO_USER(NAME, COUNT(ID) AS CNT)", new DefaultConfigStore().group("NAME"));
    }

    @Test
    public void execute(){
        String sql = "UPDATE HR_USER SET NAME =:NAME WHERE ID = :ID";
        ConfigStore configs = new DefaultConfigStore();
        configs.param("NAME", "Z").param("ID", 1);
        service.execute(sql, configs);
    }

    public void unioninit(){
        try {
            Table table = service.metadata().table("hr_user", false);
            if (null != table) {
                service.ddl().drop(table);
            }
            table = new Table("hr_user");
            table.addColumn("id", "bigint").autoIncrement(true).setPrimary(true);
            table.addColumn("code", "varchar(10)");
            table.addColumn("name", "varchar(10)");
            table.addColumn("dept_id", "bigint");
            service.ddl().create(table);

            table = service.metadata().table("hr_dept", false);
            if (null != table) {
                service.ddl().drop(table);
            }
            table = new Table("hr_dept");
            table.addColumn("id", "bigint").autoIncrement(true).setPrimary(true);
            table.addColumn("code", "varchar(10)");
            table.addColumn("name", "varchar(10)");
            service.ddl().create(table);

            table = service.metadata().table("sso_user", false);
            if (null != table) {
                service.ddl().drop(table);
            }
            table = new Table("sso_user");
            table.addColumn("id", "bigint").autoIncrement(true).setPrimary(true);
            table.addColumn("code", "varchar(10)");
            table.addColumn("name", "varchar(10)");
            table.addColumn("dept_id", "bigint");
            service.ddl().create(table);

            table = service.metadata().table("sso_dept", false);
            if (null != table) {
                service.ddl().drop(table);
            }
            table = new Table("sso_dept");
            table.addColumn("id", "bigint").autoIncrement(true).setPrimary(true);
            table.addColumn("code", "varchar(10)");
            table.addColumn("name", "varchar(10)");
            service.ddl().create(table);
        }catch (Exception e){
            e.printStackTrace();
        }


    }
    @Test
    public void json(){

        ConfigStore configs = new DefaultConfigStore();
        PageNavi navi = new DefaultPageNavi();
        navi.setCurPage(1);
        navi.setPageRows(2);
        configs.setPageNavi(navi);
        ConfigStore config1 = new DefaultConfigStore();
        config1.and("create_by", "123").or("type", "1,2,3".split(","));
//        config1.and("ID", 1);
//        config1.like("NAME", "ZH");
//        config1.in("TYPE_CODE", "1,2,3".split(","));


        ConfigStore config2 = new DefaultConfigStore();
        config2.or("update_by", "1234");

        ConfigStore config3 = new DefaultConfigStore();
        config3.or("type", 0);
//        config3.or(Compare.BETWEEN,"create_time",Arrays.asList(new Date(),new Date()));

        /**
         * 表结构转换到表中的列
         */
//        configs.columns("M.ID","F.CODE AS DEPARTMENT_CODE","");
        configs.and(config1).and(config2).or(config3);
        String json = configs.json(true);
        System.out.println(json);
        ConfigStore config4 = ConfigBuilder.build(json);

        try {
            ServiceProxy.querys("crm_user", configs);
        }catch (Exception e){

        }

        //TableBuilder.init("HR_EMPLOYEE AS M").left("HR_DEPARTMENT AS F", "F.DEPARTMENT_ID = F.ID").build()
        try {
            ServiceProxy.querys("crm_user", config4);
        }catch (Exception e){

        }
        ServiceProxy.querys("employees(name, details->>'$.age' AS age,  details->>'$.address.city' AS city)");


    }
    @Test
    public void test2(){
        ServiceProxy.querys("crm_user", 1, 9);
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        String json = "{\"columns\":{\"query\":[\"form.*\"],\"exclude\":[]},\"conditions\":{\"join\":\"AND\",\"items\":[{\"join\":\"AND\",\"items\":[{\"join\":\"AND\",\"items\":[{\"join\":\"AND\"},{\"join\":\"OR\",\"items\":[{\"join\":\"AND\",\"var\":\"user_id\",\"compare\":10,\"values\":[\"274002126321787235\"],\"over_condition\":false,\"over_value\":true,\"parser\":{\"prefix\":\"form\",\"var\":\"user_id\",\"compare\":10,\"join\":\"AND\",\"swt\":\"IGNORE\"}}]}]},{\"join\":\"OR\",\"items\":[{\"join\":\"AND\",\"var\":\"path\",\"compare\":50,\"values\":[\"575198193\"],\"over_condition\":false,\"over_value\":true,\"parser\":{\"prefix\":\"sd\",\"var\":\"path\",\"compare\":50,\"join\":\"AND\",\"swt\":\"IGNORE\"}}]}]}]},\"navi\":{\"page\":1,\"vol\":10}}";
        ConfigStore configs5 = ConfigBuilder.build(json);

        try {
            TableBuilder builder = TableBuilder.init("crm_user as form").left("crm_user as sd", "form.id = sd.id");
            ServiceProxy.querys(builder.build(), configs5);
        }catch (Exception e){

        }
    }
    @Test
    public void getSQL() {

        DataRuntime runtime = service.getDao().runtime();

        ConfigStore configStore = new DefaultConfigStore();
        configStore.and("ID", "1");
        configStore.and(Compare.NOT_IN, "ID", "100");
        configStore.and("ID > 100");
        List<Integer> between = new ArrayList<>();
        between.add(1);
        between.add(200);
        configStore.and(Compare.BETWEEN, "ID", between);
        //service.delete("crm_user", configStore);
        List<Run> runs = runtime.getAdapter().buildDeleteRun(runtime, new Table("crm_user"), configStore);

    }
    @Test
    public void count() {
        ConfigStore configs = new DefaultConfigStore();
        configs.limit(10, 20);
        //configs.page(3, 2);
       // configs.autoCount(false);
        service.querys("CRM_USER", configs);
    }

    @Test
    public void updateEmpty() {
        ConfigTable.IS_REPLACE_EMPTY_NULL = false;
        ConfigTable.IS_UPDATE_EMPTY_COLUMN = true;
        DataRow row = new DataRow();
        row.put("CODE","${''}");
        row.put("NAME","");
        row.put("id", 1);
        service.save("CRM_USER", row);
    }
    @Test
    public void sqls() {
        int result[] = service.execute("INSERT INTO CRM_USER(CODE)VALUES(1);INSERT INTO CRM_USER(CODE1)VALUES(11);", true);
        System.out.println("成功:"+result[0]);
        System.out.println("失败:"+result[1]);
    }
    @Test
    public void test() {
        StandardTypeMetadata t = StandardTypeMetadata.valueOf("VARCHAR2");
        DataSet set = new DataSet();
        for(int i=0; i<10; i++){
            DataRow row = new DataRow();
            int s = i/3;
            row.put("CODE", "CODE:"+s+";NAME:"+s);
            row.put("G", s);
            row.put("ID", i);
            set.add(row);
        }
        DataSet groups = set.group("CODE","G");
        for(DataRow group:groups){
            DataSet items = group.getItems();
            System.out.println(items);
        }
    }
    @Test
    public void array(){
        ConfigStore configs = new DefaultConfigStore();
        configs.between("ID", 1, 2);
        configs.or(Compare.BETWEEN, "ID", "11,22".split(","));
        configs.or(Compare.BETWEEN, "ID", Arrays.asList("111","222"));
        System.out.println(configs.getConfigChain().createAutoConditionChain().getRunText(null, RuntimeHolder.runtime(), true));
        service.query("SELECT * from hr_user", configs);
        for(Run run:configs.runs()){
            System.out.println(run.getFinalQuery(true));
            System.out.println(run.getFinalQuery(false));
        }
        DataSet set = new DataSet();
        for(int i=0; i<10; i++){
            DataRow row = set.add();
            row.put("ID", i);
            row.put("CODE", "c"+i);
        }
        String ids[] = new String[]{"1"};
        List<String> list = new ArrayList<>();
        list.add("2");
        System.out.println(set.select.equals("ID",ids));
        System.out.println(set.select.equals("ID",list));
    }
    @Test
    public void time(){
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        ConfigTable.IS_THROW_CONVERT_EXCEPTION =true;
        DataRow row = new DataRow();
        row.put("UPDATE_TIME", System.currentTimeMillis()+"");
        row.put("CREATE_TIME", ZonedDateTime.now());
        System.out.println(DateUtil.parse(System.currentTimeMillis()+""));
        service.insert("crm_user",row);
        //row = service.query("CRM_USER" ,"order by id desc");
        System.out.println(row);
    }
    @Test
    public void identity() throws Exception{
        DataRuntime runtime = RuntimeHolder.runtime();
        service.truncate("crm_user");
        DataSource ds = ((JdbcTemplate) runtime.getProcessor()).getDataSource();
        Connection con = ds.getConnection();
        PreparedStatement ps = con.prepareStatement("INSERT INTO CRM_USER(CODE)VALUES(1),(2),(3)", Statement.RETURN_GENERATED_KEYS);
        ps.executeUpdate();
        ResultSet rs = ps.getGeneratedKeys();
        System.out.println(JDBCUtil.keys(rs));
        ResultSetMetaData rsmd = rs.getMetaData();
        while (rs.next()){
            System.out.println(rs.getObject(1));
        }
        DataSet set = service.querys("crm_user");
        System.out.println(set);

    }
    @Test
    public void or() throws Exception{
        TableBuilder builder = TableBuilder.init("sso_user as sso")
            .left("hr_user as hr", "hr.id = sso.id");

        ConfigStore configs = new DefaultConfigStore();
        configs.and("hr.ID", "1");
        ConfigStore or = new DefaultConfigStore();
        or.like("sso.NAME","A");
        configs.or(or);
        ServiceProxy.query(builder.build(), configs);
        System.out.println(configs.getRunText(RuntimeHolder.runtime(), false));
    }
}
